﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Security.Cryptography;
using System.IO;

namespace RestFulService
{
    public class DBSERVER
    {
        LogFile Log = new LogFile();
        //私有变量，数据库连接
        private SqlConnection Connection;
        //操作 App.config https://docs.microsoft.com/zh-cn/dotnet/api/system.configuration.appsettingssection?view=dotnet-plat-ext-3.1
        //private NpgsqlConnection Connection;
        //private static string DBIP= System.Configuration.ConfigurationSettings.AppSettings["DBIP"];
        //private static string DBPORT = System.Configuration.ConfigurationSettings.AppSettings["DBPORT"];
        //private static string DBUSER = System.Configuration.ConfigurationSettings.AppSettings["DBUSER"];
        //private static string DBPW = System.Configuration.ConfigurationSettings.AppSettings["DBPW"];
        //private static string DBNAME = System.Configuration.ConfigurationSettings.AppSettings["DBNAME"];
        protected string ConnectionString;
        public ArrayList SQLList = new ArrayList();

        //构造函数
        public DBSERVER()
        {
            //ConnectionString = "Server=" + Global.sDBIP + ";Port=" + Global.sDBPORT + ";User Id=" + Global.sDBUSER + ";Password=" + Global.sDBPW + ";Database="+Global.sDBNAME;
            //ConnectionString = "Server=139.224.13.75;Port=55432;User Id=postgres;Password=Yilun123456;Database=lab;";
            string str = System.Environment.CurrentDirectory + "\\WINBOX.ini";
            IniFile ini = new IniFile(str);
            string sServer = ini.IniReadValue("DBSERVER", "SERVER");
            string sDBPORT = ini.IniReadValue("DBSERVER", "PORT");
            string sDataBase = ini.IniReadValue("DBSERVER", "DATABASE");
            string sUser = ini.IniReadValue("DBSERVER", "USER");
            string sPassWord = ini.IniReadValue("DBSERVER", "PASSWORD");

            ConnectionString = "server=" + sServer + "," + sDBPORT + ";database=" + sDataBase  + ";uid=" + sUser + ";pwd=" + sPassWord + ";integrated security=false";
            ConnectionString = ConnectionString.Replace("\0", "");
            //Console.WriteLine("数据库连接:" + ConnectionString);
        }

        //保护方法，打开数据库连接
        private void Open()
        {
            //判断数据库连接是否存在
            if (Connection == null)
            {
                //不存在，新建并打开
                Connection = new SqlConnection(ConnectionString);
                //Connection = new NpgsqlConnection(ConnectionString);
                //Log.doWriteLog(1, "数据库连接" );
                try
                {
                    Connection.Open();
                    //Console.WriteLine("数据库连接:" + ConnectionString);
                }
                catch (Exception ex)
                {
                    Console.WriteLine("数据库连接失败1:" + ex.Message);
                    Log.doWriteLog(1, "数据库连接失败1:" + ex.Message);
                }

            }
            else
            {
                try
                {
                    //存在，判断是否处于关闭状态
                    if (Connection.State.Equals(ConnectionState.Closed))
                        Connection.Open();    //连接处于关闭状态，重新打开
                }
                catch (Exception ex)
                {
                    Console.WriteLine("数据库连接失败2:" + ex.Message);
                    Log.doWriteLog(1, "显示错误:" + ex.Message);
                }
            }

        }

        //公有方法，关闭数据库连接
        public void Close()
        {
            if (Connection.State.Equals(ConnectionState.Open))
            {
                Connection.Close();     //连接处于打开状态，关闭连接
            }
        }

        //记录日志
        public void doWriteLog(int iType, string sMsg)
        {
            Log.doWriteLog(iType, sMsg);
        }

        /// <summary>
		/// 析构函数，释放非托管资源
		/// </summary>
        ~DBSERVER()
        {
            try
            {
                if (Connection != null)
                    Connection.Close();
            }
            catch { }
            try
            {
                Dispose();
            }
            catch { }
        }

        //公有方法，释放资源
        public void Dispose()
        {
            if (Connection != null)		// 确保连接被关闭
            {
                Connection.Dispose();
                Connection = null;
            }
        }

        //公有方法，执行语句
        public void doexec(string sql)
        {
            Open();
            Log.doWriteLog(1, "执行语句:" + sql);
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                Log.doWriteLog(1, "执行报错:" + ex.Message);
            }
        }

        //公有方法，根据Sql语句，返回一个Table
        public DataTable query(string sql)
        {
            Open();
            Log.doWriteLog(1, "执行查询:" + sql);
            try
            {
                SqlDataAdapter Adapter = new SqlDataAdapter(sql, Connection);
                DataSet Ds = new DataSet();
                Adapter.Fill(Ds);
                return Ds.Tables[0];
            }
            catch (Exception ex)
            {
                Log.doWriteLog(1, "查询报错:" + ex.Message);
                return null;
            }
            //Close();
        }

        //公有方法，根据Sql语句，返回一个Table
        public DataSet getDataSet(string sql)
        {
            Open();
            SqlDataAdapter Adapter = new SqlDataAdapter(sql, Connection);
            DataSet Ds = new DataSet();
            Adapter.Fill(Ds);
            //Close();
            return Ds;
        }

        //数据保存
        public void doSave(out bool IsOk, out string sError)
        {
            IsOk = true;
            sError = "成功";

            try
            {
                Open();
                SqlTransaction trans = Connection.BeginTransaction();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = Connection;
                cmd.Transaction = trans;

                foreach (string SQL in SQLList)
                {
                    Log.doWriteLog(1, "proc sql:" + SQL);
                    try
                    {
                        cmd.CommandText = SQL;
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        sError = e.Message;
                        Console.WriteLine(DateTime.Now.ToLongTimeString().ToString() + "sql error:" + e.Message);
                        Log.doWriteLog(1, "sql error:" + e.Message);
                        IsOk = false;
                        break;
                    }
                }

                if (IsOk)
                    trans.Commit();
                else
                    trans.Rollback();
            }
            catch (Exception e)
            {
                sError = e.Message;
                Console.WriteLine(DateTime.Now.ToLongTimeString().ToString() + "sql Rollback:" + e.Message);
            }

        }


        //删除历史日志文件
        public void DeleteDir(string file)
        {
            DateTime nowDate = DateTime.Now; //获取3天后的同一时刻可使用下列语句
            nowDate = nowDate.AddDays(-1 * 5);

            string sDay = nowDate.ToString("yyyyMMdd");
            try
            {
                //去除文件夹和子文件的只读属性
                //去除文件夹的只读属性
                System.IO.DirectoryInfo fileInfo = new DirectoryInfo(file);
                fileInfo.Attributes = FileAttributes.Normal & FileAttributes.Directory;

                //去除文件的只读属性
                System.IO.File.SetAttributes(file, System.IO.FileAttributes.Normal);

                //判断文件夹是否还存在
                if (Directory.Exists(file))
                {
                    foreach (string f in Directory.GetFileSystemEntries(file))
                    {
                        if (File.Exists(f))
                        {
                            string sFile = System.IO.Path.GetFileNameWithoutExtension(f);

                            if (int.Parse(sFile) < int.Parse(sDay))
                            {
                                //如果有子文件删除文件
                                File.Delete(f);
                                Console.WriteLine(f);
                            }

                        }
                        else
                        {
                            //循环递归删除子文件夹
                            DeleteDir(f);
                        }
                    }

                    //删除空文件夹
                    Directory.Delete(file);
                    Console.WriteLine(file);
                }

            }
            catch (Exception ex) // 异常处理
            {
                Console.WriteLine(ex.Message.ToString());// 异常信息
            }
        }
    }
}
